using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Data.Odbc;
using System.Collections;
using System.Threading;
using CocaCola_Inventory_System.Reports;
using CocaCola_Inventory_System.Forms;

namespace CocaCola_Inventory_System
{
    public partial class search : Form
    {
        Panel[] panels;
        DB ds;
        OdbcConnection con;
        ArrayList criteria;
        DataTable locations;
        string last="";
        string whereclause = "where";
        public search()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            
        }

        private void button23_Click(object sender, EventArgs e)
        {

        }

        private void search_Load(object sender, EventArgs e)
        {
            if (((CocaCola_Inventory_System.Forms.Container)this.MdiParent).security < 1)
            {
                MessageBox.Show("Not Authorized");
                return;
            }


            con = ((CocaCola_Inventory_System.Forms.Container)this.MdiParent).con; 
            panels = new Panel[14];
            criteria = new ArrayList();
            panels[0] = panel1;
            panels[1] = panel2;
            panels[2] = panel3;
            panels[3] = panel4;
            panels[4] = panel14;
            panels[5] = panel15;
            panels[6] = panel5;
            panels[7] = panel7;
            panels[8] = panel6;
            panels[9] = panel8;
            panels[10] = panel9;
            panels[11] = panel10;
            panels[12] = panel11;
            panels[13] = panel12;
            listBox1.SelectedIndex = 0;
            retrieve();
            ds = new DB();

            
            
        }

        private void panel3_Paint(object sender, PaintEventArgs e)
        {

        }

        private void listBox2_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void listBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            if (listBox1.SelectedIndex < 0 || listBox1.SelectedIndex >= listBox1.Items.Count)
                return;

            foreach (Panel p in panels)
                p.Visible = false;

            panels[listBox1.SelectedIndex].Visible = true;
            panels[listBox1.SelectedIndex].Location = new Point(230, 20);
            panels[listBox1.SelectedIndex].BringToFront();

        }
        private void retrieve()
        {
            
            if (con.State != ConnectionState.Open)
                con.Open();
            OdbcCommand com = new OdbcCommand();
            com.Connection = con;
            com.CommandText = "select name from types";
            OdbcDataReader reader = com.ExecuteReader();
            listBox3.Items.Clear();
            while (reader.Read())
                listBox3.Items.Add(reader[0]);
            reader.Dispose();
            com.CommandText = "select name from detail_types";
            reader = com.ExecuteReader();
            listBox4.Items.Clear();
            while (reader.Read())
                listBox4.Items.Add(reader[0]);
            reader.Dispose();
            /*com.CommandText = "select location from items";
           reader= com.ExecuteReader();
            ArrayList a = new ArrayList();
            while (reader.Read())
                a.Add(reader[0]);
            object [] res = removeduplicates(a.ToArray());
            listBox5.Items.Clear();
            foreach (object o in res)
                listBox5.Items.Add(o);
            */
            retrieve r = new retrieve(((CocaCola_Inventory_System.Forms.Container)this.MdiParent).con);
            locations = r.getlocations();
            listBox5.DataSource = locations;
            listBox5.DisplayMember = "name";
            listBox5.ValueMember = "name";
            listBox2.Items.Clear();
            com.CommandText = "select name from conditions";
            reader.Dispose();
            reader = com.ExecuteReader(); 

            while (reader.Read())
                listBox2.Items.Add(reader[0].ToString());

            reader.Dispose();
            /*com.CommandText = "select department from items";
            reader = com.ExecuteReader();
            a = new ArrayList();
            while (reader.Read())
                a.Add(reader[0]);
            res = removeduplicates(a.ToArray());
            listBox6.Items.Clear();
            foreach (object o in res)
                listBox6.Items.Add(o);*/
            //if (con.State != ConnectionState.Closed)
               // con.Close();
            }
        
        public object[] removeduplicates(object[] l)
        {
            ArrayList res = new ArrayList();
            for (int i = 0; i < l.Length; i++)
                if (!res.Contains(l[i]))
                    res.Add(l[i]);
            res.TrimToSize();
            return res.ToArray();

        }

        private void button2_Click(object sender, EventArgs e)
        {
            if(listBox1.SelectedItems.Count<1)
                MessageBox.Show("No Search Criteria To Add");
            string query = "(";
           
                if (checkBox1.Checked)
                {
                    query += "NOT ";
                }
                if (listBox1.SelectedIndex == 0)
                {
                    if (textBox1.Text == "")
                        return;
                    query += "items.Serial_no like  '%" + textBox1.Text + "%'";
                    listBox7.Items.Add((checkBox1.Checked?"NOT ":"")+"Serial Number : " + textBox1.Text);
                }
                if (listBox1.SelectedIndex == 1)
                {
                    if (textBox2.Text == "")
                        return;
                    query += "items.name like  '%" + textBox2.Text.Replace(";", "%' OR items.name like '%") + "%'";
                    listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Item Name : " + textBox2.Text.Replace(";", " OR "));
                }
                if (listBox1.SelectedIndex == 2)
                {
                    if (listBox2.SelectedIndex<0 || listBox2.SelectedIndex>=listBox2.Items.Count)
                        return;
                    query += "items.cond like  '%" + listBox2.SelectedItem.ToString() + "%'";
                    listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Condition : " + listBox2.SelectedItem.ToString());
                }
                if (listBox1.SelectedIndex == 3)
                {
                    if (textBox3.Text == "")
                        return;
                    if (radioButton3.Checked)
                    {

                        query += "items.acq_value = " + textBox3.Text;
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Acq Value = " + textBox3.Text);
                    }
                    else if (radioButton1.Checked)
                    {
                        query += "items.acq_value > " + textBox3.Text;
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Acq Value > " + textBox3.Text);
                    }
                    else if (radioButton2.Checked)
                    {
                        query += "items.acq_value < " + textBox3.Text;
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Acq Value < " + textBox3.Text);
                    }
                }
                if (listBox1.SelectedIndex == 4)
                {

                    if (textBox4.Text == "")
                        return;
                    if (radioButton4.Checked)
                    {
                        query += "items.accum_value =  " + textBox4.Text;
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Accum Value = " + textBox4.Text);
                    }
                    else if (radioButton6.Checked)
                    {
                        query += "items.accum_value >  " + textBox4.Text;
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Accum Value > " + textBox4.Text);
                    }
                    else if (radioButton5.Checked)
                    {
                        query += "items.accum_value <  " + textBox4.Text;
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Accum Value < " + textBox4.Text);
                    }
                }
                if (listBox1.SelectedIndex == 5)
                {
                    if (textBox5.Text == "")
                        return;
                    if (radioButton7.Checked == true)
                    {
                        query += "items.book_value = " + textBox5.Text;
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Book Value = " + textBox5.Text);
                    }
                    else if (radioButton9.Checked == true)
                    {
                        query += "items.book_value > " + textBox5.Text;
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Book Value > " + textBox5.Text);
                    }
                    else if (radioButton8.Checked == true)
                    {
                        query += "items.book_value < " + textBox5.Text;
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Book Value < " + textBox5.Text);
                    } 
                }
                if (listBox1.SelectedIndex == 6)
                {

                    if (radioButton10.Checked == true)
                    {
                        query += "items.purchase_date = '" + dateTimePicker1.Value.Year + "-" + dateTimePicker1.Value.Month + "-" + dateTimePicker1.Value.Day + "'";
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Purchase Date = " + dateTimePicker1.Value.ToShortDateString());
                    }
                    else if (radioButton12.Checked == true)
                    {
                        query += "items.purchase_date > '" + dateTimePicker1.Value.Year + "-" + dateTimePicker1.Value.Month + "-" + dateTimePicker1.Value.Day + "'";
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Purchase Date > " + dateTimePicker1.Value.ToShortDateString());                  
                    }
                    else if (radioButton11.Checked == true)
                    {
                        query += "items.purchase_date < '" + dateTimePicker1.Value.Year+"-"+dateTimePicker1.Value.Month+"-"+dateTimePicker1.Value.Day+"'";
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Purchase Date < " + dateTimePicker1.Value.ToShortDateString()); 
                    } 
                }
                if (listBox1.SelectedIndex == 7)
                {
                    if (textBox6.Text == "")
                        return;
                    query += "(items.refresh_category in (select id from category where name like '%" + textBox6.Text + "%'))";
                    listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Refresh Category : " + textBox6.Text);
                }
                if (listBox1.SelectedIndex == 8)
                {
                    if (radioButton13.Checked)
                    {
                        query += "items.refresh_date = '" + dateTimePicker2.Value.Year + "-" + dateTimePicker2.Value.Month + "-" + dateTimePicker2.Value.Day + "'";
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Refresh Date = " + dateTimePicker2.Value.ToShortDateString()); 
                    }
                    else if (radioButton15.Checked)
                    {
                        query += "items.refresh_date > '" + dateTimePicker2.Value.Year + "-" + dateTimePicker2.Value.Month + "-" + dateTimePicker2.Value.Day + "'";
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Refresh Date > " + dateTimePicker2.Value.ToShortDateString()); 
                    }
                    else if (radioButton14.Checked)
                    {
                        query += "items.refresh_date < '" + dateTimePicker2.Value.Year + "-" + dateTimePicker2.Value.Month + "-" + dateTimePicker2.Value.Day + "'";
                        listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Refresh Date < " + dateTimePicker2.Value.ToShortDateString()); 
                     }                
                }
                if (listBox1.SelectedIndex == 9)
                {
                    if (listBox3.SelectedIndex < 0 || listBox3.SelectedIndex >= listBox3.Items.Count)
                        return;
                    query += "(type_id in(select id from types where name  like '%" + listBox3.SelectedItem.ToString() + "%'))";
                    listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Type : " + listBox3.SelectedItem.ToString());
                }
                if (listBox1.SelectedIndex == 10)
                {
                    if (textBox7.Text == "")
                        return;
                    query += " (items.id in (select items.id  from items,users where users.id = (select user_id from ownership where item_id = items.id order by start_date desc limit 1) AND (users.name like '%"+textBox7.Text+"%')))";
                    listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "User : " + textBox7.Text);
                }
                if (listBox1.SelectedIndex == 11)
                {
                    if (listBox4.SelectedIndex < 0 || listBox4.SelectedIndex >= listBox4.Items.Count)
                        return;

                    if (textBox8.Text == "")
                        return;

                    query += "items.id in (select item_id from details where (detail_type_id in (select id from detail_types where name like '%" + listBox4.SelectedItem.ToString() + "%')AND ( value like '%" + textBox8.Text + "%')))";
                    listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Type : " + listBox4.SelectedItem.ToString() + "         Value = " + textBox8.Text);
                }
                if (listBox1.SelectedIndex == 12)
                {
                    if (listBox5.SelectedIndex < 0 || listBox5.SelectedIndex >= listBox5.Items.Count)
                        return;
                    query += "(items.location_id in (select id from locations where name like '" + locations.Rows[listBox5.SelectedIndex][1].ToString() + "'))";
                    listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Location: " + locations.Rows[listBox5.SelectedIndex][1].ToString());
                }
                if (listBox1.SelectedIndex == 13)
                {
                    if (listBox5.SelectedIndex < 0 || listBox5.SelectedIndex >= listBox5.Items.Count)
                        return;
                    query += "(id in (select item_id from ownership where user_id in (select id from users where department like '%"+listBox6.SelectedItem.ToString()+"%' )))";
                    listBox7.Items.Add((checkBox1.Checked ? "NOT " : "") + "Location: " + locations.Rows[listBox5.SelectedIndex][1].ToString());
                }
                query += ")";
                checkBox1.Checked = false;
                criteria.Add(query);
                
            
        }

        private void button1_Click_1(object sender, EventArgs e)
        {
            foreach (DataTable t in ds.Tables)
                t.Clear();
            if (criteria.Count < 1)
            {
                button9_Click(null, null);
                return;
            }
            if (con.State != ConnectionState.Open)
                con.Open();


            whereclause = "where ";
            object[] cr = criteria.ToArray();
            string q = "select items.id,items.name,acq_value,users.name 'Assigned To',locations.name 'Location' ,users.Department 'Department',accum_value,book_value,purchase_date,category.name 'Refresh Category',refresh_date,types.name 'Item Type',ad_id,serial_no 'Serial Number',Cond 'Status',Tagging,Location 'Premsis',depresionation.name 'Depresionation Category' from items , types,category,depresionation,users,locations where";
            for (int i = 0; i < cr.Length - 1; i++)
            {
                q += cr[i].ToString() + " AND ";
                whereclause += cr[i].ToString() + " AND ";
            }
            q += cr[cr.Length - 1];
            whereclause += cr[cr.Length - 1];
            q += " AND (depresionation.id = items.depres) AND (items.type_id = types.id) AND (items.Refresh_Category = category.id) AND(users.id = (select user_id from ownership where item_id = items.id order by start_date desc limit 1))AND (items.location_id = locations.id)";
            last = q;
            
            /*OdbcCommand se = new OdbcCommand(q);

            se.Connection = con;

            
            OdbcDataAdapter adp = new OdbcDataAdapter();

            foreach (DataTable t in ds.Tables)
                t.Clear();

            adp.SelectCommand = se;
            adp.Fill(ds.Tables["items"]);
            adp.SelectCommand.CommandText = "select * from users where id in (select user_id from ownership where item_id in(" + q.Replace("*", "id") + "))";
            adp.Fill(ds.Tables["users"]);
            adp.SelectCommand.CommandText = "select * from detail_types ";
            adp.Fill(ds.Tables["detail_types"]);
            adp.SelectCommand.CommandText = "select * from types";
            adp.Fill(ds.Tables["types"]);
            adp.SelectCommand.CommandText = "select * from details where item_id in ("+q.Replace("*","id")+")";
            adp.Fill(ds.Tables["details"]);
            adp.SelectCommand.CommandText = "select * from ownership where item_id in (" + q.Replace("*", "id") + ")";
            adp.Fill(ds.Tables["ownership"]);



            dataGridView1.DataSource = ds.Tables["items"];
            //MessageBox.Show(ds.Tables["items"].Rows.Count.ToString());
            criteria.Clear();
            listBox7.Items.Clear();
            dataGridView1.Columns[0].Visible = false;
            dataGridView1.Columns[8].Visible = false;
            dataGridView1.Columns[9].Visible = false;
            tabControl1.SelectTab(1);*/
            toolStripStatusLabel1.Text = "Processing";
            textBox9.Text = "";
            textBox10.Text = "";
            //refe();
            if(!backgroundWorker1.IsBusy)
            backgroundWorker1.RunWorkerAsync();
        }

        private void button4_Click(object sender, EventArgs e)
        {
            if (textBox2.Text != "" && (!textBox2.Text.EndsWith(";")))
                textBox2.Text = textBox2.Text.ToString() + ";";
        }

        private void button3_Click(object sender, EventArgs e)
        {
            if (listBox7.SelectedIndex < 0 ||listBox7.SelectedIndex >= listBox7.Items.Count)
                return;

            criteria.RemoveAt(listBox7.SelectedIndex);
            listBox7.Items.RemoveAt(listBox7.SelectedIndex);

        }

        private void textBox9_TextChanged(object sender, EventArgs e)
        {
            string filter = "";


            if (textBox9.Text != "")
                filter += "Name Like '*" + textBox9.Text+"*' AND";

            if (textBox10.Text != "")
                filter += " Location Like '*" + textBox10.Text + "*' AND";
            if(textBox11.Text!="")
                filter += " [SN] Like '*" + textBox11.Text + "*' AND";
            /*
            if(textBox10.Text!="")
              if(textBox9.Text!="")
                  filter+= " AND Location Like '*"+textBox10.Text+"*'";
              else
                  filter += " Location Like '*" + textBox10.Text + "*'";
            */
            ds.View.Columns["serial number"].ColumnName = "SN";
            if (filter != "")
                filter = filter.Substring(0, filter.Length - 3);
            ds.Tables["view"].DefaultView.RowFilter =filter;
            ds.View.Columns["SN"].ColumnName = "Serial Number";
        }

        private void button5_Click(object sender, EventArgs e)
        {
            if (((CocaCola_Inventory_System.Forms.Container)this.MdiParent).security < 1)
            {
                MessageBox.Show("Not Authorized");
                return;
            }
            ReportForm f = new ReportForm();
            try
            {
                switch (listBox8.SelectedIndex)
                {
                    case 0:
                        ReportBynameGrouped r1 = new ReportBynameGrouped();
                        f.ReportViewer.ReportSource = r1;
                        r1.SetDataSource(ds);
                        break;
                    case 1:
                        ReportByname r2 = new ReportByname();
                        f.ReportViewer.ReportSource = r2;
                        r2.SetDataSource(ds);
                        break;
                    case 2:
                        reportByLocationGrouped r3 = new reportByLocationGrouped();
                        f.ReportViewer.ReportSource = r3;
                        r3.SetDataSource(ds);
                        break;
                    case 3:
                        reportByLocation r4 = new reportByLocation();
                        f.ReportViewer.ReportSource = r4;
                        r4.SetDataSource(ds);
                        break;
                    case 4:
                        owners ow = new owners();
                        f.ReportViewer.ReportSource = ow;
                        ow.SetDataSource(ds);                        
                        break;
                    case 5:
                        Departments dep = new Departments();
                        f.ReportViewer.ReportSource = dep;
                        dep.SetDataSource(ds);
                        break;
                    case 6:
                        AllData ad = new AllData();
                        f.ReportViewer.ReportSource = ad;
                        ad.SetDataSource(ds);
                        break;
                    default:
                        return;
                }
            }
            catch (Exception)
            {

            }
            f.Show();
            
        }

        private void button6_Click(object sender, EventArgs e)
        {
            if (((CocaCola_Inventory_System.Forms.Container)this.MdiParent).security < 2)
            {
                MessageBox.Show("Not Authorized");
                return;
            }
            if (dataGridView1.SelectedCells.Count < 1)
                return;
            if (dataGridView1.SelectedCells[0].RowIndex < 0 || dataGridView1.SelectedCells[0].RowIndex >= dataGridView1.Rows.Count)
            {

                return;
            }


            Insert i = new Insert(((CocaCola_Inventory_System.Forms.Container)this.MdiParent).con);
            if (i.deleteRecord(Convert.ToInt32(dataGridView1.SelectedCells[0].OwningRow.Cells[0].Value), ((CocaCola_Inventory_System.Forms.Container)this.MdiParent).id))
               MessageBox.Show("Record Deleted");
            else
               MessageBox.Show("Failed To Delete Record");
           toolStripStatusLabel1.Text = "Processing";
           //refe();
            if(!backgroundWorker1.IsBusy)
           backgroundWorker1.RunWorkerAsync();
        }

        private void button7_Click(object sender, EventArgs e)
        {
            if (((CocaCola_Inventory_System.Forms.Container)this.MdiParent).security < 2)
            {
                MessageBox.Show("Not Authorized");
                return;
            }
            if (dataGridView1.SelectedCells.Count < 1)
                return;
            if (dataGridView1.SelectedCells[0].RowIndex < 0 || dataGridView1.SelectedCells[0].RowIndex >= dataGridView1.Rows.Count)
            {

                return;
            }
            //MessageBox.Show(.Value.ToString());
            Update u = new Update();
            u.MdiParent = this.MdiParent;
            u.parent = this;
            u.id = Convert.ToInt32(dataGridView1.SelectedCells[0].OwningRow.Cells[0].Value.ToString());
            u.Show();
        }

        public void refresh_data()
        {
            
            
            string q = last;
            if (last == "")
                return;
            OdbcCommand se = new OdbcCommand(q);

            se.Connection = con;
            //q.Replace("select *", "select items.id,items.name,acq_value,accum_value,book_value,purchase_date,category.name,refresh_date,types.name,ad_id,serial_no,Cond,Tagging,Location,depresionation.name from items , types,category,depresionation");

            OdbcDataAdapter adp = new OdbcDataAdapter();

            foreach (DataTable t in ds.Tables)
                t.Clear();

            
            adp.SelectCommand = se;
            adp.Fill(ds.Tables["View"]);
            adp.Fill(ds.Tables["Export"]);
            adp.SelectCommand.CommandText = "select * from items " + whereclause;
            adp.Fill(ds.Tables["items"]);
            adp.SelectCommand.CommandText = "select * from users ";
            adp.Fill(ds.Tables["users"]);
            adp.SelectCommand.CommandText = "select * from detail_types ";
            adp.Fill(ds.Tables["detail_types"]);
            adp.SelectCommand.CommandText = "select * from types";
            adp.Fill(ds.Tables["types"]);
            adp.SelectCommand.CommandText = "select * from details where item_id in ( select id from items " +whereclause+")";
            adp.Fill(ds.Tables["details"]);
            adp.SelectCommand.CommandText = "select * from ownership where item_id in ( select id from items " + whereclause +")";
            adp.Fill(ds.Tables["OwnerShip"]);
            adp.SelectCommand.CommandText = "select * from Locations ";
            adp.Fill(ds.Tables["Locations"]);
            adp.SelectCommand.CommandText = "select * from Countries ";
            adp.Fill(ds.Tables["Countries"]);
            adp.SelectCommand.CommandText = "select * from BU ";
            adp.Fill(ds.Tables["BU"]);

            
            //MessageBox.Show(ds.Tables["items"].Rows.Count.ToString());
            criteria.Clear();
            
        }

        private void transactionsToolStripMenuItem_Click(object sender, EventArgs e)
        {

            Form1 f = new Form1();
            f.Show();
        }

        

        private void tabControl1_Enter(object sender, EventArgs e)
        {
            
        }

        private void tabPage3_Enter(object sender, EventArgs e)
        {
            Form1 f = new Form1();
            f.Show();
            tabControl1.SelectTab(0);
        }

        private void button8_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedCells.Count < 1)
                return;
            if (dataGridView1.SelectedCells[0].RowIndex < 0 || dataGridView1.SelectedCells[0].RowIndex >= dataGridView1.Rows.Count)
            {

                return;
            }

            

            Histroy h = new Histroy();
            h.id = Convert.ToInt32(dataGridView1.SelectedCells[0].OwningRow.Cells[0].Value.ToString());
            h.Show();
        }

        private void textBox10_TextChanged(object sender, EventArgs e)
        {
            string filter = "";
            if (textBox10.Text != "")
                filter += " Location Like '*" + textBox10.Text + "*'";
            if (textBox9.Text != "")
                filter += "Name Like '*" + textBox9.Text + "*'";
            ds.Tables["items"].DefaultView.RowFilter = filter;
        }

        private void tabPage2_Click(object sender, EventArgs e)
        {

        }

        private void textBox8_TextChanged(object sender, EventArgs e)
        {

        }

        private void viewHistoryToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (dataGridView1.SelectedCells.Count < 1)
                return;
            if (dataGridView1.SelectedCells[0].RowIndex < 0 || dataGridView1.SelectedCells[0].RowIndex >= dataGridView1.Rows.Count)
            {

                return;
            }



            Histroy h = new Histroy();
            h.id = Convert.ToInt32(dataGridView1.SelectedCells[0].OwningRow.Cells[0].Value.ToString());
            h.MdiParent = this.MdiParent;
            h.Show();
        }

        private void updateToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (((CocaCola_Inventory_System.Forms.Container)this.MdiParent).security < 2)
            {
                MessageBox.Show("Not Authorized");
                return;
            }
            if (dataGridView1.SelectedCells.Count < 1)
                return;
            if (dataGridView1.SelectedCells[0].RowIndex < 0 || dataGridView1.SelectedCells[0].RowIndex >= dataGridView1.Rows.Count)
            {

                return;
            }
            //MessageBox.Show(.Value.ToString());
            Update u = new Update();
            u.MdiParent = this.MdiParent;
            u.parent = this;
            u.id = Convert.ToInt32(dataGridView1.SelectedCells[0].OwningRow.Cells[0].Value.ToString());
            u.Show();
        }

        private void deleteToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (((CocaCola_Inventory_System.Forms.Container)this.MdiParent).security < 2)
            {
                MessageBox.Show("Not Authorized");
                return;
            }
            if (dataGridView1.SelectedCells.Count < 1)
                return;
            if (dataGridView1.SelectedCells[0].RowIndex < 0 || dataGridView1.SelectedCells[0].RowIndex >= dataGridView1.Rows.Count)
            {

                return;
            }


            Insert i = new Insert(((CocaCola_Inventory_System.Forms.Container)this.MdiParent).con);
            if (i.deleteRecord(Convert.ToInt32(dataGridView1.SelectedCells[0].OwningRow.Cells[0].Value), ((CocaCola_Inventory_System.Forms.Container)this.MdiParent).id))
                MessageBox.Show("Record Deleted");
            else
                MessageBox.Show("Failed To Delete Record");
            toolStripStatusLabel1.Text = "Processing";
            //refe();
            if (!backgroundWorker1.IsBusy)
              backgroundWorker1.RunWorkerAsync();
        }

        private void dataGridView1_MouseUp(object sender, MouseEventArgs e)
        {
            
        }

        private void dataGridView1_MouseClick(object sender, MouseEventArgs e)
        {
            
        }

        private void dataGridView1_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button != MouseButtons.Right)
                return;

            dataGridView1.CurrentCell = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex];
        }

        private void dataGridView1_CellMouseUp(object sender, DataGridViewCellMouseEventArgs e)
        {
            if (e.Button != MouseButtons.Right)
                return;

            dataGridView1.CurrentCell = dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex];
        }

        private void tabControl1_DrawItem(object sender, DrawItemEventArgs e)
        {
            TabPage CurrentTab = tabControl1.TabPages[e.Index];
            Rectangle ItemRect = tabControl1.GetTabRect(e.Index);
            SolidBrush FillBrush = new SolidBrush(Color.Navy);
            SolidBrush TextBrush = new SolidBrush(Color.WhiteSmoke);
            StringFormat sf = new StringFormat();
            sf.Alignment = StringAlignment.Center;
            sf.LineAlignment = StringAlignment.Center;

            //If we are currently painting the Selected TabItem we'll
            //change the brush colors and inflate the rectangle.
            if (System.Convert.ToBoolean(e.State & DrawItemState.Selected))
            {
                FillBrush.Color = Color.DarkGray;
                TextBrush.Color = Color.Maroon;
                ItemRect.Inflate(2, 2);
            }

            //Set up rotation for left and right aligned tabs
            if (tabControl1.Alignment == TabAlignment.Left || tabControl1.Alignment == TabAlignment.Right)
            {
                float RotateAngle = 90;
                if (tabControl1.Alignment == TabAlignment.Left)
                    RotateAngle = 270;
                PointF cp = new PointF(ItemRect.Left + (ItemRect.Width / 2), ItemRect.Top + (ItemRect.Height / 2));
                e.Graphics.TranslateTransform(cp.X, cp.Y);
                e.Graphics.RotateTransform(RotateAngle);
                ItemRect = new Rectangle(-(ItemRect.Height / 2), -(ItemRect.Width / 2), ItemRect.Height, ItemRect.Width);
            }

            //Next we'll paint the TabItem with our Fill Brush
            e.Graphics.FillRectangle(FillBrush, ItemRect);

            //Now draw the text.
            e.Graphics.DrawString(CurrentTab.Text, e.Font, TextBrush, RectangleF.Inflate(ItemRect, ItemRect.X, ItemRect.Y), sf);

            //Reset any Graphics rotation
            e.Graphics.ResetTransform();

            //Finally, we should Dispose of our brushes.
            FillBrush.Dispose();
            TextBrush.Dispose();
        }

        private void refreshToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if(!backgroundWorker1.IsBusy)
            backgroundWorker1.RunWorkerAsync();
            //refe();
        }

        private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e)
        {
            
            refresh_data();
        }

        public void refresh()
        {
            toolStripStatusLabel1.Text = "Processing";
            //refe();
            if (!backgroundWorker1.IsBusy)
                backgroundWorker1.RunWorkerAsync();
        }

        private void backgroundWorker1_RunWorkerCompleted(object sender, RunWorkerCompletedEventArgs e)
        {
            listBox7.Items.Clear();
            //MessageBox.Show(ds.Tables["view"].Rows.Count.ToString());
            dataGridView1.DataSource = ds.View;
            dataGridView1.Columns[0].Visible = false;
            //MessageBox.Show(dataGridView1.RowCount.ToString());
            //dataGridView1.Columns[8].Visible = false;
            
            dataGridView1.Columns[10].Visible = false;
            /*dataGridView1.Columns[3].HeaderText = "Acquisition Value";
            
            dataGridView1.Columns[4].HeaderText = "Accumilative Value";
            dataGridView1.Columns[5].HeaderText = "Book Value";
            dataGridView1.Columns[6].HeaderText = "Purchase Date";
            dataGridView1.Columns[8].HeaderText = "Refresh Date";
            dataGridView1.Columns[13].HeaderText = "Capital Tagging";
            dataGridView1.Columns[14].HeaderText = "Location";*/
            //dataGridView1.Columns[4].HeaderText = "Accumilative Value";

            tabControl1.SelectTab(1);
            toolStripStatusLabel1.Text = "Ready";
            ds.Tables["view"].DefaultView.RowFilter = "name like 'a'";
            ds.Tables["view"].DefaultView.RowFilter = "";
            
            //MessageBox.Show(dataGridView1.RowCount.ToString());
            dataGridView1.DataSource = ds.Tables["view"];
            
        
        }

        public void refe()
        {
            string q = last;
            if (last == "")
                return;
            OdbcCommand se = new OdbcCommand(q);

            se.Connection = con;
            //q.Replace("select *", "select items.id,items.name,acq_value,accum_value,book_value,purchase_date,category.name,refresh_date,types.name,ad_id,serial_no,Cond,Tagging,Location,depresionation.name from items , types,category,depresionation");

            OdbcDataAdapter adp = new OdbcDataAdapter();

            foreach (DataTable t in ds.Tables)
                t.Clear();

            adp.SelectCommand = se;
            adp.Fill(ds.Tables["View"]);
            adp.SelectCommand.CommandText = "select * from items " + whereclause;
            adp.Fill(ds.Tables["items"]);
            adp.SelectCommand.CommandText = "select * from users ";
            adp.Fill(ds.Tables["users"]);
            adp.SelectCommand.CommandText = "select * from detail_types ";
            adp.Fill(ds.Tables["detail_types"]);
            adp.SelectCommand.CommandText = "select * from types";
            adp.Fill(ds.Tables["types"]);
            adp.SelectCommand.CommandText = "select * from details where item_id in ( select id from items " + whereclause + ")";
            adp.Fill(ds.Tables["details"]);
            adp.SelectCommand.CommandText = "select * from ownership where item_id in ( select id from items " + whereclause + ")";
            adp.Fill(ds.Tables["OwnerShip"]);



            //MessageBox.Show(ds.Tables["items"].Rows.Count.ToString());
            criteria.Clear();
            listBox7.Items.Clear();
            dataGridView1.DataSource = ds.Tables["view"];
            //dataGridView1.Columns[0].Visible = false;

            //dataGridView1.Columns[8].Visible = false;
            // dataGridView1.Columns[9].Visible = false;

            tabControl1.SelectTab(1);
            toolStripStatusLabel1.Text = "Ready";
            //ds.Tables["view"].DefaultView.RowFilter = "a";
            //ds.Tables["view"].DefaultView.RowFilter = "";

            MessageBox.Show(dataGridView1.RowCount.ToString());
        }

        private void dataGridView1_DataError(object sender, DataGridViewDataErrorEventArgs e)
        {

        }

        private void endAssignmentToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (((CocaCola_Inventory_System.Forms.Container)this.MdiParent).security < 2)
            {
                MessageBox.Show("Not Authorized");
                return;
            }
            if (dataGridView1.SelectedCells.Count < 1)
                return;
            if (dataGridView1.SelectedCells[0].RowIndex < 0 || dataGridView1.SelectedCells[0].RowIndex >= dataGridView1.Rows.Count)
            {

                return;
            }
            Insert i = new Insert(((CocaCola_Inventory_System.Forms.Container)this.MdiParent).con);
            i.endownership(Convert.ToInt32(dataGridView1.SelectedCells[0].OwningRow.Cells[0].Value), DateTime.Now, ((CocaCola_Inventory_System.Forms.Container)this.MdiParent).id, ((CocaCola_Inventory_System.Forms.Container)this.MdiParent).stockid);
            this.refresh_data();
        }

        private void button9_Click(object sender, EventArgs e)
        {
            
            foreach (DataTable t in ds.Tables)
                t.Clear();
            
            if (con.State != ConnectionState.Open)
                con.Open();


            whereclause = "where (items.name like '%')";
            object[] cr = criteria.ToArray();
            string q = "select items.id,users.name 'Assigned To',locations.name 'Location',users.Department 'Department',items.name,acq_value,accum_value,book_value,purchase_date,category.name 'Refresh Category',refresh_date,types.name 'Item Type',ad_id,serial_no 'Serial Number',Cond 'Status',Tagging,Location 'Premsis',depresionation.name 'Depresionation Category' from items , types,category,depresionation,users,locations where";
            /*for (int i = 0; i < cr.Length - 1; i++)
            {
                q += cr[i].ToString() + " AND ";
                whereclause += cr[i].ToString() + " AND ";
            }
            q += cr[cr.Length - 1];
            whereclause += cr[cr.Length - 1];*/
            q += "(items.name like '%')";
            q += " AND (depresionation.id = items.depres) AND (items.type_id = types.id) AND (items.Refresh_Category = category.id) AND(users.id = (select user_id from ownership where item_id = items.id order by start_date desc limit 1)) AND (locations.id = items.location_id)";
            last = q;

            
            toolStripStatusLabel1.Text = "Processing";
            textBox9.Text = "";
            textBox10.Text = "";
            //refe();
            if (!backgroundWorker1.IsBusy)
                backgroundWorker1.RunWorkerAsync();
        }

        private void dataGridView1_DoubleClick(object sender, EventArgs e)
        {
            
        }

        private void dataGridView1_MouseDoubleClick(object sender, MouseEventArgs e)
        {
            /*
            if (((CocaCola_Inventory_System.Forms.Container)this.MdiParent).security < 2)
            {
                MessageBox.Show("Not Authorized");
                return;
            }
            if (dataGridView1.SelectedCells.Count < 1)
                return;
            if (dataGridView1.SelectedCells[0].RowIndex < 0 || dataGridView1.SelectedCells[0].RowIndex >= dataGridView1.Rows.Count)
            {

                return;
            }
            //MessageBox.Show(.Value.ToString());
            Update u = new Update();
            u.MdiParent = this.MdiParent;
            u.parent = this;
            u.id = Convert.ToInt32(dataGridView1.SelectedCells[0].OwningRow.Cells[0].Value.ToString());
            u.Show();
             */
        }

        private void listBox8_SelectedIndexChanged(object sender, EventArgs e)
        {

        }

        private void button10_Click(object sender, EventArgs e)
        {
            ExportToExcel ex = new ExportToExcel();
            ex.ExportTable = ds.Export;
            ex.Show();
        }

        private void printLabelCardToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (((CocaCola_Inventory_System.Forms.Container)this.MdiParent).security < 2)
            {
                MessageBox.Show("Not Authorized");
                return;
            }
            if (dataGridView1.SelectedCells.Count < 1)
                return;
            if (dataGridView1.SelectedCells[0].RowIndex < 0 || dataGridView1.SelectedCells[0].RowIndex >= dataGridView1.Rows.Count)
            {

                return;
            }
            int id = Convert.ToInt32(dataGridView1.SelectedCells[0].OwningRow.Cells[0].Value);
            retrieve r = new retrieve(((CocaCola_Inventory_System.Forms.Container)this.MdiParent).con);
            DB data = r.ItemCard(id);
            LabelCard lc = new LabelCard();
            lc.SetDataSource(data);
            ReportForm f = new ReportForm();
            f.ReportViewer.ReportSource = lc;
            f.MdiParent = this.MdiParent;
            f.Show();
        }

        private void addToRecievingReportToolStripMenuItem_Click(object sender, EventArgs e)
        {
            if (((CocaCola_Inventory_System.Forms.Container)this.MdiParent).security < 2)
            {
                MessageBox.Show("Not Authorized");
                return;
            }
            if (dataGridView1.SelectedCells.Count < 1)
                return;
            if (dataGridView1.SelectedCells[0].RowIndex < 0 || dataGridView1.SelectedCells[0].RowIndex >= dataGridView1.Rows.Count)
            {

                return;
            }
            int id = Convert.ToInt32(dataGridView1.SelectedCells[0].OwningRow.Cells[0].Value);
            string name = dataGridView1.SelectedCells[0].OwningRow.Cells["name"].Value.ToString();
            string serial= dataGridView1.SelectedCells[0].OwningRow.Cells["serial number"].Value.ToString();
            DateTime purchasedate= Convert.ToDateTime(dataGridView1.SelectedCells[0].OwningRow.Cells["purchase_date"].Value);
            retrieve r = new retrieve(((CocaCola_Inventory_System.Forms.Container)this.MdiParent).con);
            int userid = r.GetUseridforitem(id);
            if (userid < 0)
            {
                MessageBox.Show("Failed To Add Item to recieving report");
                return;
            }
            RecievingReport rp ;
            bool found = false;
            foreach (Form f in this.MdiParent.MdiChildren)
            {
                try
                {
                    rp = (RecievingReport)f;
                }
                catch
                {
                    continue;
                }
                if (rp.userid == userid)
                {
                    rp.AddItem(id, name, serial, purchasedate);
                    found = true;
                }
            }
            if (!found)
            {
                RecievingReport rcv = new RecievingReport(userid);
                rcv.MdiParent = this.MdiParent;
                rcv.userid = userid;
                rcv.AddItem(id, name, serial, purchasedate);
                rcv.Show();
            }

        }

        

        
    }
}